package com.statistics.mapper;

import com.alibaba.fastjson2.JSONObject;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * VisualStatisticsMapper
 *
 * @author chenyy
 * &#064;date 2025/3/4 17:01
 */
public interface VisualStatisticsMapper {

    // 纵向项目 总项目数,本年度项目数
    @Select("select count(*) as projectSum, " +
            "count( case when year( authorize_date ) = year(curdate()) then 1 end ) as yearProjectNum " +
            "from biz_zx_project where checkstatus = 2")
    JSONObject getZxStatistics();

    // 横向项目 总项目数,本年度项目数
    @Select("select count(*) as projectSum, " +
            "count( case when year( authorize_date ) = year(curdate()) then 1 end ) as yearProjectNum " +
            "from biz_yf_contract where checkstatus = 2")
    JSONObject getHxStatistics();

    // 科研成果[总计,本年度] 学术论文,学术著作,研究报告,鉴定成果,艺术作品,专利,著作权,标准,药证,新品种
    @Select("select count(*) as achievementSum, ifnull(sum(case when publish_year = year(curdate()) then 1 else 0 end),0) as yearAchievementSum " +
            "from ( select checkstatus, year(publish_date) as publish_year from biz_paper " +
            "union all select checkstatus, year(publish_date) as publish_year from biz_book " +
            "union all select checkstatus, year(submit_date) as publish_year from biz_research_report " +
            "union all select checkstatus, year(appraisal_date) as publish_year from biz_appraisal_product " +
            "union all select checkstatus, year(publication_date) as publish_year from biz_art_product " +
            "union all select checkstatus, year(apply_date) as publish_year from biz_patent " +
            "union all select checkstatus, year(publication_date) as publish_year from biz_copyright " +
            "union all select checkstatus, year(public_date) as publish_year from biz_standard " +
            "union all select checkstatus, year(authorize_date) as publish_year from biz_medicine " +
            "union all select checkstatus, year(authorize_date) as publish_year from biz_breed) as alias " +
            "where checkstatus = 2")
    JSONObject getAchieveStats();

    // 科研学术成果[总计,本年度] 主办会议,学术讲座,参加会议,人员派遣,人员接受
    @Select("select count(*) as achievementSum, ifnull(sum(case when publish_year = year(curdate()) then 1 else 0 end),0) as yearAchievementSum " +
            "from (select checkstatus, year(begin_date) as publish_year from biz_meeting " +
            "union all select checkstatus, year(lecture_date) as publish_year from biz_lecture " +
            "union all select checkstatus, year(meeting_date) as publish_year from biz_join_meeting" +
            " union all select checkstatus, year(begin_date) as publish_year from biz_personnel_dispatch " +
            "union all select checkstatus, year(begin_date) as publish_year from biz_personnel_accept ) as alias " +
            "where checkstatus = 2")
    JSONObject getAcadAchieveStats();

    // 科研人员总数
    @Select("select (select count(*) from biz_person where checkstatus = 2) + (select count(*) from biz_expert where checkstatus = 2 and person_id is null) as totalResearchers")
    JSONObject getTotalResearchers();

    // 近五年度学校项目量
    @Select("with recursive years as ( select year(curdate()) - 4 as annual union all select annual + 1 from years where annual < year(curdate())) " +
            "select y.annual, COALESCE(xj.count, 0) +  COALESCE(zx.count, 0) +  COALESCE(yf.count, 0) as projectNum " +
            "from years y " +
            "left join (select year(authorize_date) yr, count(*) count  from biz_xj_project where checkstatus = 2 group by yr) xj on y.annual = xj.yr " +
            "left join (select year(authorize_date) yr, count(*) count from biz_zx_project where checkstatus = 2 group by yr) zx on y.annual = zx.yr " +
            "left join (select year(authorize_date) yr, count(*) count from biz_yf_contract where checkstatus = 2 group by yr) yf on y.annual = yf.yr " +
            "order by y.annual")
    List<JSONObject> getFiveYProjectsStats();

    // 本年度学院科研立项经费TOP5 [项目总经费,到账经费]
    @Select("with allprojects as (select id, unit_id, fee_authorize, year(authorize_date) as project_year from biz_zx_project where year(authorize_date) = year(curdate())" +
            " and checkstatus = 2 union all select id, unit_id, fee_authorize, year(authorize_date) as project_year from biz_yf_contract where year(authorize_date) = year ( curdate())" +
            " and checkstatus = 2 union all select id, unit_id, fee_authorize, year(authorize_date) as project_year from biz_xj_project where year(authorize_date) = year (curdate()) " +
            "and checkstatus = 2 ), project_merge as (select p.unit_id unitId, u.name as unitName, SUM(COALESCE(p.fee_authorize, 0)) as authorize from allprojects p " +
            "join biz_unit u on p.unit_id = u.id group by p.unit_id )select * from project_merge order by authorize desc limit 5")
    List<JSONObject> getResearchFundingTop5();

    // 根据单位ID获取本年度到账经费
    @Select("select ifnull(sum(income_fee),0) as incomes from biz_project_income where checkstatus = 2 and year ( income_date ) = year (curdate()) and unit_id = #{unitId}")
    String getIncomeByUnitId(@Param("unitId") String unitId);

    // 近五年论文发表
    @Select("with recursive years as ( select year(curdate()) - 4 as annual union all select annual + 1 from years where annual < year(curdate())) " +
            "select y.annual, count( bp.id ) as paperNum from years y left join biz_paper bp on y.annual = year(bp.publish_date ) " +
            "and bp.checkstatus = 2 group by y.annual order by y.annual")
    List<JSONObject> getFiveYPaperStats();

    // 本年度学校按项目级别的立项情况 [国家级 部级 省级 地市级 校级（纵校+校级）横向课题(横向项目)]
    @Select("select count(case when project_level_id = 1 then 1 end) as nationalLevel, count(case when project_level_id = 2 then 1 end) " +
            "as ministerialLevel, count(case when project_level_id = 3 then 1 end) as provinciallevel, count(case when project_level_id = 4 then 1 end) " +
            "as municipalLevel, count(case when project_level_id = 5 then 1 end) + (select count(id) from biz_xj_project where checkstatus = 2 " +
            "and year(authorize_date) = year(curdate())) as schoolLevel, (select count(id) from biz_yf_contract where checkstatus = 2 " +
            "and year(authorize_date) = year(curdate())) as horizontalProject from biz_zx_project where year(authorize_date) = year(curdate()) " +
            "and checkstatus = 2")
    JSONObject getProjectLevelStats();


    // 本年度项目经费 [纵,横,校]
    @Select("select (select ifnull(sum(fee_authorize), 0) from biz_zx_project where year(authorize_date) = year(curdate()) and checkstatus = 2) " +
            "as zxExpenditure, (select ifnull(sum(fee_authorize), 0) from biz_yf_contract where year(authorize_date) = year(curdate()) and checkstatus = 2) " +
            "as hxExpenditure, (select ifnull(sum(fee_authorize), 0) from biz_xj_project where year(authorize_date) = year(curdate()) and checkstatus = 2) " +
            "as xjExpenditure")
    JSONObject getProjectExpenditure();

    // 本年度到账经费 [纵,横,校]
    @Select("select (select ifnull(sum(income_fee), 0) from biz_zx_project bzp left join biz_project_income bpi on bzp.id = bpi.project_id " +
            "where year(income_date) = year(curdate()) and bzp.checkstatus = 2) as zxFundsReceived, " +
            "(select ifnull(sum(income_fee), 0) from biz_yf_contract byc left join biz_project_income bpi on byc.id = bpi.project_id " +
            "where year(income_date) = year(curdate()) and byc.checkstatus = 2) as hxFundsReceived, (select ifnull(sum(income_fee), 0) " +
            "from biz_xj_project bxp left join biz_project_income bpi on bxp.id = bpi.project_id where year(income_date) = year (curdate()) " +
            "and bxp.checkstatus = 2) as xjFundsReceived")
    JSONObject getProjectFundsReceived();

    // 近五年专利发表
    @Select("with recursive years as ( select year(curdate()) - 4 as annual union all select annual + 1 from years where annual < year(curdate())) " +
            "select y.annual, count( bp.id ) as patentNum from years y " +
            "left join biz_patent bp on y.annual = year(bp.apply_date ) and bp.checkstatus = 2 " +
            "group by y.annual  order by y.annual")
    List<JSONObject> getFiveYPatentStats();

    // 本年度学院成果TOP5
    @Select("select bu.name as unitName, (coalesce(papers.count, 0) + coalesce(books.count, 0) + coalesce(researchs.count, 0) + " +
            "coalesce(appraisals.count, 0) + coalesce(arts.count, 0) + coalesce(patents.count, 0) + coalesce(copyrights.count, 0) + " +
            "coalesce(standards.count, 0) + coalesce(medicines.count, 0) + coalesce(breeds.count, 0) + coalesce(meetings.count, 0) + " +
            "coalesce(lectures.count, 0) + coalesce(jomes.count, 0) + coalesce(dispatchs.count, 0) + coalesce(accepts.count, 0)) as  totalAchievement " +
            "from biz_unit bu left join ( " +
            "select unit_id, count(*) as count from biz_paper where year(publish_date) = year(curdate()) and checkstatus = 2 group by unit_id ) papers on bu.id = papers.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_book where year(publish_date) = year(curdate()) and checkstatus = 2 group by unit_id ) books on bu.id = books.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_research_report where year(submit_date) = year(curdate()) and checkstatus = 2 group by unit_id ) researchs on bu.id = researchs.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_appraisal_product where year(appraisal_date) = year(curdate()) and checkstatus = 2 group by unit_id ) appraisals on bu.id = appraisals.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_art_product where year(publication_date) = year(curdate()) and checkstatus = 2 group by unit_id ) arts on bu.id = arts.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_patent where year(apply_date) = year(curdate()) and checkstatus = 2 group by unit_id ) patents on bu.id = patents.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_copyright where year(publication_date) = year(curdate()) and checkstatus = 2 group by unit_id ) copyrights on bu.id = copyrights.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_standard where year(public_date) = year(curdate()) and checkstatus = 2 group by unit_id ) standards on bu.id = standards.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_medicine where year(authorize_date) = year(curdate()) and checkstatus = 2 group by unit_id ) medicines on bu.id = medicines.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_breed where year(authorize_date) = year(curdate()) and checkstatus = 2 group by unit_id ) breeds on bu.id = breeds.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_meeting where year(begin_date) = year(curdate()) and checkstatus = 2 group by unit_id ) meetings on bu.id = meetings.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_lecture where year(lecture_date) = year(curdate()) and checkstatus = 2 group by unit_id ) lectures on bu.id = lectures.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_join_meeting where year(meeting_date) = year(curdate()) and checkstatus = 2 group by unit_id ) jomes on bu.id = jomes.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_personnel_dispatch where year(begin_date) = year(curdate()) and checkstatus = 2 group by unit_id ) dispatchs on bu.id = dispatchs.unit_id " +
            "left join ( select unit_id, count(*) as count from biz_personnel_accept where year(begin_date) = year(curdate()) and checkstatus = 2 group by unit_id ) accepts on bu.id = accepts.unit_id " +
            "order by  totalachievement desc limit 5")
    List<JSONObject> getUnitAchievementStatsTop5();

    // 近五年成果数量
    @Select("with recursive years as ( select year(curdate()) - 4 as annual union all select annual + 1 from years where annual < year(curdate()))  " +
            "select y.annual, COALESCE(paper.count, 0) + COALESCE(book.count, 0) + COALESCE(research.count, 0) + COALESCE(appraisal.count, 0) + COALESCE(art.count, 0) + " +
            "COALESCE(patent.count, 0) + COALESCE(copyright.count, 0) + COALESCE(standard.count, 0) + COALESCE(medicine.count, 0) + COALESCE(breed.count, 0) + " +
            "COALESCE(meeting.count, 0) + COALESCE(lecture.count, 0) + COALESCE(jome.count, 0) + COALESCE(dispatch.count, 0) + COALESCE(accept.count, 0) AS totalAchievement " +
            "from years y  " +
            "left join ( select year(publish_date) yr, count(*) count from biz_paper where checkstatus = 2 group by yr) paper on y.annual = paper.yr " +
            "left join ( select year(publish_date) yr, count(*) count from biz_book where checkstatus = 2 group by yr) book on y.annual = book.yr " +
            "left join ( select year(submit_date) yr, count(*) count from biz_research_report where checkstatus = 2 group by yr) research on y.annual = research.yr " +
            "left join ( select year(appraisal_date) yr, count(*) count from biz_appraisal_product where checkstatus = 2 group by yr) appraisal on y.annual = appraisal.yr " +
            "left join ( select year(publication_date) yr, count(*) count from biz_art_product where checkstatus = 2 group by yr) art on y.annual = art.yr " +
            "left join ( select year(apply_date) yr, count(*) count from biz_patent where checkstatus = 2 group by yr) patent on y.annual = patent.yr " +
            "left join ( select year(publication_date) yr, count(*) count from biz_copyright where checkstatus = 2 group by yr) copyright on y.annual = copyright.yr " +
            "left join ( select year(public_date) yr, count(*) count from biz_standard where checkstatus = 2 group by yr) standard on y.annual = standard.yr " +
            "left join ( select year(authorize_date) yr, count(*) count from biz_medicine where checkstatus = 2 group by yr) medicine on y.annual = medicine.yr " +
            "left join ( select year(authorize_date) yr, count(*) count from biz_breed where checkstatus = 2 group by yr) breed on y.annual = breed.yr " +
            "left join ( select year(begin_date) yr, count(*) count from biz_meeting where checkstatus = 2 group by yr) meeting on y.annual = meeting.yr " +
            "left join ( select year(lecture_date) yr, count(*) count from biz_lecture where checkstatus = 2 group by yr) lecture on y.annual = lecture.yr " +
            "left join ( select year(meeting_date) yr, count(*) count from biz_join_meeting where checkstatus = 2 group by yr) jome on y.annual = jome.yr " +
            "left join ( select year(begin_date) yr, count(*) count from biz_personnel_dispatch where checkstatus = 2 group by yr) dispatch on y.annual = dispatch.yr " +
            "left join ( select year(begin_date) yr, count(*) count from biz_personnel_accept where checkstatus = 2 group by yr) accept on y.annual = accept.yr " +
            "order by y.annual")
    List<JSONObject> getFiveYTotalAchievement();

    // 近五年获奖情况
    @Select("with recursive years as ( select year(curdate()) - 4 as annual union all select annual + 1 from years where annual < year(curdate())) " +
            "select y.annual, count( bh.id ) as honorNum from years y " +
            "left join biz_honor bh on y.annual = year(bh.honor_date) and bh.checkstatus = 2 " +
            "group by y.annual order by y.annual")
    List<JSONObject> getFiveYHonorStats();

}